﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BLToolkit.Data;
using BLToolkit.Data.DataProvider;
using System.Data;
using System.Runtime.Serialization.Formatters.Binary;
using System.IO;
using nwrole.toolbox.sync.extensions;
using nwrole.toolbox.sync.entity;
using nwrole.toolbox.sync.Utils;

namespace nwrole.toolbox.sync
{
    public class GhostTableSyncXXX
    {

        public string SourceConnectionString { get; set; }
        public string DestinationConnectionString { get; set; }
        public DataProviderBase sourceDataProvider { get; set; } // new OracleDataProvider()
        public DataProviderBase destinationDataProvider { get; set; } // new OracleDataProvider()
        public SqlBuilder sqlBuilder { get; set; }
        public int BatchSize { get; set; }

        private IDbDataParameter[] GetParameters(DbManager command, SyncMap map, DataRow row, bool onlyPrimaryKeys)
        {
            int paramCount = map.Columns.Count;
            if (onlyPrimaryKeys)
            {
                paramCount = map.PrimaryKeyColumns.Count();
            }

            IDbDataParameter[] paramArray = new IDbDataParameter[paramCount];
            int pos = 0;
            if (!onlyPrimaryKeys)
            {
                foreach (var column in map.NonPrimaryKeyColumns)
                {
                    object value = row[column.ColumnName];
                    paramArray[pos] = command.Parameter(SqlBuilder.parameterPrefix + column.ColumnName, value);
                    pos++;
                }
            }

            foreach (var column in map.PrimaryKeyColumns)
            {
                object value = row[column.ColumnName];
                paramArray[pos] = command.Parameter(SqlBuilder.parameterPrefix + column.ColumnName, value);
                pos++;
            }
            // object val = row[map.PrimaryKey.ColumnName];
            // paramArray[pos] = command.Parameter(SqlBuilder.parameterPrefix + map.PrimaryKey.ColumnName, val);
            return paramArray;
        }

        private IDbDataParameter[] GetParameters(DbManager command, SyncMap map, IDataReader reader, bool onlyPrimaryKeys)
        {
            int paramCount = map.Columns.Count;
            if (onlyPrimaryKeys)
            {
                paramCount = map.PrimaryKeyColumns.Count();
            }

            IDbDataParameter[] paramArray = new IDbDataParameter[paramCount];
            int pos = 0;
            if (!onlyPrimaryKeys)
            {
                foreach (var column in map.NonPrimaryKeyColumns)
                {
                    object value = reader[column.ColumnName];
                    paramArray[pos] = command.Parameter(SqlBuilder.parameterPrefix + column.ColumnName, value);
                    pos++;
                }
            }

            foreach (var column in map.PrimaryKeyColumns)
            {
                object value = reader[column.ColumnName];
                paramArray[pos] = command.Parameter(SqlBuilder.parameterPrefix + column.ColumnName, value);
                pos++;
            }
            // object val = row[map.PrimaryKey.ColumnName];
            // paramArray[pos] = command.Parameter(SqlBuilder.parameterPrefix + map.PrimaryKey.ColumnName, val);
            return paramArray;
        }


        public void RestoreMetadata(SyncMap map)
        {
            DbManager.DefaultConfiguration = ""; //to reset possible previous changes            
            using (DbManager db = new DbManager(sourceDataProvider, SourceConnectionString))
            {
                string deleteSql = sqlBuilder.DeleteFromShadowTable(map);
                db.SetCommand(deleteSql).ExecuteNonQuery();

                string copySql = sqlBuilder.SelectIntoShadowTable(map);
                db.SetCommand(copySql).ExecuteNonQuery();
            }            
        }

        public void Sync(SyncMap map, Func<DataTable, int> doit)
        {
            DbManager.DefaultConfiguration = ""; //to reset possible previous changes            
            using (DbManager db = new DbManager(sourceDataProvider, SourceConnectionString))
            {

                db.SetCommand("update " + GlobalSettings.SqlTablePrefix + ".OBJEKTSBUNDET_COPY set BELOPP = 130 where OBJEKTSBUNDETID = 21");
                db.ExecuteNonQuery();

                // hämta alla ändringar
                string selectSql = sqlBuilder.SelectBatchDiff(map, BatchSize);
                // sql1 = "select * from V_Bo_Omrade";
                DataTable changeSet = db.SetCommand(selectSql).ExecuteDataTable();

                // gör något med ändringarna
                doit(changeSet);

                // update ghost table
                foreach (DataRow row in changeSet.Rows)
                {
                    string updateSql = sqlBuilder.UpsertShadowTable(map); ;
                    var command = db.SetCommand(updateSql, GetParameters(db, map, row, true));
                    
                    command.ExecuteNonQuery();
                }
            }
        }
        
        public List<SyncEntity> CreateSyncMapList(string schema, string[] tableNames)
        {
            List<SyncEntity> list = new List<SyncEntity>();
            DbManager.DefaultConfiguration = ""; //to reset possible previous changes            
            using (DbManager db = new DbManager(sourceDataProvider, SourceConnectionString))
            {
                int counter = 0;
                foreach (string tableName in tableNames) 
                {
                    System.Console.Write("Creating SyncMap " + tableName + " ....");
                    SyncEntity entity = CreateSyncMapPriv(db, schema, tableName);
                    entity.SortIndex = counter++;
                    entity.SyncEnabled = 1;
                    list.Add(entity);
                    System.Console.WriteLine("Done.");
                }
            }
            return list;
        }

        public SyncEntity CreateSyncMap(string schema, string tableName)
        {
            DbManager.DefaultConfiguration = ""; //to reset possible previous changes            
            using (DbManager db = new DbManager(sourceDataProvider, SourceConnectionString))
            {
                return CreateSyncMapPriv(db, schema, tableName);
            }
        }

        public SyncMap CreateSyncMapPriv(DbManager db, string schema, string tableName)
        {

                // http://www.orafaq.com/forum/t/151939/2/
                // SELECT  table_name,column_name FROM  information_schema.columns WHERE   column_name LIKE '%whateverYouWant%'

                // select * from all_tables where owner = 'WF'
                // select * from ALL_TAB_COLUMNS where owner = 'WF' and TABLE_NAME = 'HYRESDEL'


                string sql2 = @"
select * from ALL_TAB_COLUMNS cols1 
left join  all_cons_columns cols2 
on cols1.COLUMN_NAME = cols2.COLUMN_NAME and cols1.TABLE_NAME = cols2.TABLE_NAME  
left join all_constraints cons
on cons.CONSTRAINT_NAME = cols2.CONSTRAINT_NAME and cons.TABLE_NAME = cols2.TABLE_NAME and cons.CONSTRAINT_TYPE = 'P'
where cols1.owner = '{0}' and cols1.TABLE_NAME = '{1}'  
order by COLUMN_ID
";

                string sql3 = @"
select * from ALL_TAB_COLUMNS cols1 
left join  all_cons_columns cols2 
on cols1.COLUMN_NAME = cols2.COLUMN_NAME and cols1.TABLE_NAME = cols2.TABLE_NAME  and cols2.owner = cols1.owner 
left join all_constraints cons
on cons.CONSTRAINT_NAME = cols2.CONSTRAINT_NAME and cons.TABLE_NAME = cols2.TABLE_NAME and cons.CONSTRAINT_TYPE = 'P'
where cols1.owner = '{0}' and cols1.TABLE_NAME = '{1}'  
and ((COLS2.COLUMN_NAME is null and CONS.CONSTRAINT_NAME is null) or (COLS2.COLUMN_NAME is not null and CONS.CONSTRAINT_NAME is not null)) 
order by COLUMN_ID
";

                string sql = @"
select cols1.COLUMN_NAME, cols1.DATA_TYPE, cols1.NULLABLE, cols1.DATA_LENGTH, cols1.DATA_PRECISION,cols1.DATA_SCALE, cons.CONSTRAINT_TYPE  
from all_cons_columns cols2 
inner join all_constraints cons
on cons.CONSTRAINT_NAME = cols2.CONSTRAINT_NAME and cons.TABLE_NAME = cols2.TABLE_NAME and cons.CONSTRAINT_TYPE = 'P'
right join ALL_TAB_COLUMNS cols1 
on cols1.COLUMN_NAME = cols2.COLUMN_NAME and cols1.TABLE_NAME = cols2.TABLE_NAME  and cols2.owner = cols1.owner 
where cols1.owner = '{0}' and cols1.TABLE_NAME = '{1}'  
order by COLUMN_ID
";

                db.SetCommand(String.Format(sql, schema, tableName));
                DataTable myDataTable = db.ExecuteDataTable();

                SyncMap map = new SyncMap();
                int counter = 0;
                foreach(DataRow row in myDataTable.Rows) 
                {
                    string COLUMN_NAME = row["COLUMN_NAME"].ToString();
                    string DATA_TYPE = row["DATA_TYPE"].ToString();
                    string NULLABLE = row["NULLABLE"].ToString();
                    string DATA_LENGTH = row["DATA_LENGTH"].ToString();
                    string DATA_PRECISION = row["DATA_PRECISION"].ToString();
                    string DATA_SCALE = row["DATA_SCALE"].ToString();
                    string CONSTRAINT_TYPE = (row["CONSTRAINT_TYPE"] != null) ? row["CONSTRAINT_TYPE"].ToString() : "";
                    if (DATA_TYPE.Equals("LONG"))
                    {
                        COLUMN_NAME = COLUMN_NAME + "_CLOB";
                    }

                    SyncMapColumnType SourceSqlType = DbTypeUtil.MakeOracleType(COLUMN_NAME, DATA_TYPE, NULLABLE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE);
                    string CSHARPTYPE = DbTypeUtil.MakeCSharpType(DATA_TYPE);
                    SyncMapColumnType _DestSqlType = DbTypeUtil.MakeTsqlType(COLUMN_NAME, SourceSqlType, DATA_TYPE, NULLABLE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE);
                    bool _PrimaryKey = "P".Equals(CONSTRAINT_TYPE);

                    if ((COLUMN_NAME.ToLower() != "senasddddtforlangt") && (COLUMN_NAME.ToLower() != "kotid"))
                    {
                        SyncMapColumn col = new SyncMapColumn()
                        {
                            ColumnName = COLUMN_NAME,
                            CSharpType = CSHARPTYPE,
                            SourceSqlTypeName = SourceSqlType.TypeName,
                            SourceSqlTypeNullableBool = SourceSqlType.TypeNullable,
                            DestSqlTypeName = _DestSqlType.TypeName,
                            DestSqlTypeNullableBool = _DestSqlType.TypeNullable,
                            PrimaryKey = _PrimaryKey
                        };
                        col.SortIndex = counter++;
                        col.SyncEnabled = 1;
                        map.AddColumn(col);
                    }
                }

                map.ShadowTableName = tableName + "_ghost";
                map.DestinationTableName = "INT_" + tableName;
                map.Name = "FAST_" + tableName;
                map.SourceTableName = "wf." + tableName;
                //myDataTable.TableName = tableName;
                //StringBuilder tableSchemaBuilder = new StringBuilder();
                //myDataTable.WriteXmlSchema(new StringWriter(tableSchemaBuilder));
                //string tableSchema = tableSchemaBuilder.ToString();
                //string result = tableSchema.Replace("<", "\r\n<");
                //System.Console.WriteLine(result);
                //int test = 1;
                map.SyncEnabled = 1;
                map.ResyncAll = 0;
                return map;
        }

        /*public void SyncWithReader(SyncMap map, Func<byte[], string, string, int> callback)
        {
            DbManager.DefaultConfiguration = ""; //to reset possible previous changes            
            using (DbManager db = new DbManager(sourceDataProvider, SourceConnectionString))
            {

                db.SetCommand("update " + GlobalSettings.SqlTablePrefix + ".OBJEKTSBUNDET_COPY set BELOPP = 130 where OBJEKTSBUNDETID = 21");
                db.ExecuteNonQuery();

                db.SetCommand("delete from " + GlobalSettings.SqlTablePrefix + ".OBJEKTSBUNDET_COPY");
                db.ExecuteNonQuery();

                // hämta alla ändringar
                string selectSql = sqlBuilder.SelectBatchDiff(map, BatchSize);
                // sql1 = "select * from V_Bo_Omrade";

                db.SetCommand(selectSql);
                IDataReader reader = db.ExecuteReader();

                BinaryFormatter serializationFormatter = new BinaryFormatter();
                MemoryStream buffer = new MemoryStream();

                // string schema = sqlBuilder.GetColumnNames(map.Columns).Replace(",", "\t");
                string schema = map.GetSchema();


                object[] row = null;
                string updateSql = sqlBuilder.UpsertShadowTable(map);

                while (reader.Read())
                {
                    if (row == null) {
                        row = new object[reader.FieldCount];
                    }

                    // copy from reader to object[]
                    for (int t = 0; t < reader.FieldCount; t++)
                    {
                        row[t] = reader[t];
                    }

                    serializationFormatter.Serialize(buffer, row);
                    if (buffer.Length > 1000000)
                    {
                        callback(buffer.ToArray(), map.Name, schema);
                        buffer.SetLength(0);
                    }

                    // update ghost table
                    var updateCommand = db.SetCommand(updateSql, GetParameters(db, map, reader, true));
                    int result2 = updateCommand.ExecuteNonQuery();
                }

                if (buffer.Length > 0)
                {
                    callback(buffer.ToArray(), map.Name, schema);
                    buffer.SetLength(0);
                }

            }
        }*/

        // SELECT dbms_metadata.get_ddl('TABLE', '<TABLE_NAME>', '<SCHEMA>') FROM dual;



        public SyncResult SyncWithReaderAndDirectUpdate(SyncEntity map, Func<byte[], string, string, int> callback)
        {
            SyncResult result = new SyncResult();
            DbManager.DefaultConfiguration = ""; //to reset possible previous changes            
            using (DbManager db = new DbManager(sourceDataProvider, SourceConnectionString))
            {

                if (map.Name == "FAST_TEXTER")
                {
                    try
                    {
                        string dropSql = sqlBuilder.DropOracleTable(GlobalSettings.SqlTablePrefix + ".TEXTER_CLOB");
                        db.SetCommand(dropSql); // "drop table dbo.TEXTER_CLOB");
                        db.ExecuteNonQuery(); 

                        string sql = @"
create GLOBAL TEMPORARY table " + GlobalSettings.SqlTablePrefix + @".TEXTER_CLOB ON COMMIT PRESERVE ROWS
as select TEXTERID, TEXTERTYPID, TABELLNAMN, RELID, to_lob(text) as TEXT_CLOB 
from wf.texter
where tabellnamn = 'Objekt'";
                        db.SetCommand(sql);
                        db.ExecuteNonQuery();
                    }
                    catch
                    {
                    }

                }

                if (map.Name.ToLower().Contains("OBJEKTSBUNDET".ToLower()))
                {
                    db.SetCommand("update " + GlobalSettings.SqlTablePrefix + ".OBJEKTSBUNDET_GHOST set BELOPP = 130 where OBJEKTSBUNDETID = 21");
                    db.ExecuteNonQuery();
                }

                // db.SetCommand("delete from dbo.OBJEKTSBUNDET_COPY");
                // db.ExecuteNonQuery();

                if (!string.IsNullOrEmpty(map.SourceSyncStartSPName))
                {
                    // kör procedur om det finns någon sådan konfigurerad
                    db.SetCommand(map.SourceSyncStartSPName);
                    db.ExecuteNonQuery();
                }

                // save the syncdate
                DateTime tmp_SYNCDAT = DateTime.Now;
                DateTime SYNCDAT = new DateTime(tmp_SYNCDAT.Year, tmp_SYNCDAT.Month, tmp_SYNCDAT.Day, tmp_SYNCDAT.Hour, tmp_SYNCDAT.Minute, tmp_SYNCDAT.Second);

                if (map.SyncMode == SyncModeEnum.GhostSync)
                {
                    if (map.ResyncAll == 1)
                    {
                        string deleteSql = sqlBuilder.DeleteFromShadowTable(map);
                        db.SetCommand(deleteSql);
                        db.ExecuteNonQuery();
                        map.ResyncAll = 0;
                    }

                    // update ghost table
                    string directUpsertSql = sqlBuilder.DirectUpsertShadowTable(map);

                    if (directUpsertSql.ToLower().Contains("KUNDINTRESSENTTYP".ToLower()))
                    {
                    }

                    db.SetCommand(directUpsertSql, db.Parameter(GlobalSettings.SqlVariablePrefix + "SYNCDAT1", SYNCDAT), db.Parameter(GlobalSettings.SqlVariablePrefix + "SYNCDAT2", SYNCDAT));
                    db.ExecuteNonQuery();

                    // get changes from ghost table
                    string selectSql = sqlBuilder.SelectFromShadowTableBySYNCDAT(map);
                    db.SetCommand(selectSql, db.Parameter(GlobalSettings.SqlVariablePrefix + "SYNCDAT", SYNCDAT));
                }
                else if (map.SyncMode == SyncModeEnum.LastChangedSync)
                {
                    //db.SetCommand("select getdate()");
                    db.SetCommand("select dateadd(minute, -1, getdate())");
                    result.newSyncDat = (DateTime)db.ExecuteScalar();

                    string extraFilter = map.SourceFilter;

                    if (!string.IsNullOrEmpty(map.SourceTimestampColumnName))
                    {
                        // Sync using TIMESTAMP

                        // First get the highest timestamp from the result
                        string maxSql = sqlBuilder.MaxTimestampFromSourceTableByTimestamp(map, extraFilter, map.OverrideFilter);
                        db.SetCommand(maxSql, db.Parameter(GlobalSettings.SqlVariablePrefix + "HighestTimestamp", map.HighestTimestamp, DbType.Binary));
                        result.HighestTimestamp = db.ExecuteScalar<System.Data.Linq.Binary>();

                        string selectSql = sqlBuilder.SelectFromSourceTableByTimestamp(map, extraFilter, map.OverrideFilter);

                        db.SetCommand(selectSql, db.Parameter(GlobalSettings.SqlVariablePrefix + "HighestTimestamp", map.HighestTimestamp, DbType.Binary));
                    }
                    else
                    {
                        // Sync using SYNCDAT (Datetime)
                        DateTime lastChanged = map.SyncDat.HasValue ? map.SyncDat.Value : new DateTime(1900, 1, 1);

                        string selectSql = sqlBuilder.SelectFromSourceTableBySYNCDAT(map, extraFilter, map.OverrideFilter);

                        db.SetCommand(selectSql, db.Parameter(GlobalSettings.SqlVariablePrefix + "SYNCDAT", lastChanged));
                    }
                    
                    if (map.ResyncAll == 1)
                    {
                        map.ResyncAll = 0;
                    }

                }

                IDataReader reader = db.ExecuteReader();

                BinaryFormatter serializationFormatter = new BinaryFormatter();
                MemoryStream buffer = new MemoryStream();
                string schema = map.GetSchema();

                object[] row = null;
                while (reader.Read())
                {
                    result.RowCount++;
                    if (row == null)
                    {
                        row = new object[reader.FieldCount];
                    }

                    // copy from reader to object[]
                    for (int t = 0; t < reader.FieldCount; t++)
                    {
                        var col = map.Columns[t];
                        try
                        {
                            row[t] = reader[t];
                        }
                        catch
                        {
                        }

                    }

                    serializationFormatter.Serialize(buffer, row);
                    if (buffer.Length > 1000000)
                    {
                        callback(buffer.ToArray(), map.Name, schema);
                        buffer.SetLength(0);
                    }

                }

                if (buffer.Length > 0)
                {
                    callback(buffer.ToArray(), map.Name, schema);
                    buffer.SetLength(0);
                }

            }
            return result;
        }


        /*public void CreateGhostInt(SyncEntity map, bool drop)
        {

            if (map.SourceTableName.Equals(map.ShadowTableName, StringComparison.CurrentCultureIgnoreCase))
            {
                throw new Exception("map.SourceTableName.Equals(map.ShadowTableName)");
            }

            DbManager.DefaultConfiguration = ""; //to reset possible previous changes            
            using (DbManager db = new DbManager(sourceDataProvider, SourceConnectionString))
            {
                if (drop)
                {
                    string dropTableSql = sqlBuilder.DropGhostTable(map);
                    db.SetCommand(dropTableSql);
                    db.ExecuteNonQuery();
                }

                string createTableSql = sqlBuilder.CreateGhostTable(map);
                db.SetCommand(createTableSql);
                db.ExecuteNonQuery();

            }
        }*/

        /*public void CreateDestinationInt(SyncEntity map)
        {
            try
            {
                DbManager.DefaultConfiguration = ""; //to reset possible previous changes            
                using (DbManager db = new DbManager(destinationDataProvider, DestinationConnectionString))
                {
                    string dropDropInsertSP = sqlBuilder.DropInsertSP(map);
                    db.SetCommand(dropDropInsertSP).ExecuteNonQuery();

                    string dropTableSql = sqlBuilder.DropTable(map);
                    db.SetCommand(dropTableSql).ExecuteNonQuery();

                    string DropTypeSql = sqlBuilder.DropType(map);
                    db.SetCommand(DropTypeSql).ExecuteNonQuery();

                    string createTableSql = sqlBuilder.CreateTable(map);
                    db.SetCommand(createTableSql).ExecuteNonQuery();

                    string sql2 = sqlBuilder.CreateType(map);
                    db.SetCommand(sql2).ExecuteNonQuery();

                    string sql1 = sqlBuilder.CreateInsertSP(map);
                    db.SetCommand(sql1).ExecuteNonQuery();

                    map.DeployDestinationDat = DateTime.Now;
                }
            }
            catch
            {
            }
        }*/

        public void CreateImportInt(SyncEntity map)
        {
            DbManager.DefaultConfiguration = ""; //to reset possible previous changes            
            using (DbManager db = new DbManager(destinationDataProvider, DestinationConnectionString))
            {
                string dropImportSP = sqlBuilder.DropImportSP(map);
                db.SetCommand(dropImportSP).ExecuteNonQuery();

                string sql1 = sqlBuilder.CreateImportSP(map);
                db.SetCommand(sql1).ExecuteNonQuery();

            }
        }

        /*public void CreateSourceEnvironment()
        {
            try
            {
                DbManager.DefaultConfiguration = ""; //to reset possible previous changes            
                using (DbManager db = new DbManager(sourceDataProvider, SourceConnectionString))
                {
                    string createSyncEntityTable = sqlBuilder.CreateSyncEntityTable();
                    db.SetCommand(createSyncEntityTable).ExecuteNonQuery();

                    string createSyncEntityColumnTable = sqlBuilder.CreateSyncEntityColumnTable();
                    db.SetCommand(createSyncEntityColumnTable).ExecuteNonQuery();
                }
            }
            catch
            {
            }
        }*/

        /*public void CreateDestEnvironment()
        {
            try
            {
                DbManager.DefaultConfiguration = ""; //to reset possible previous changes            
                using (DbManager db = new DbManager(destinationDataProvider, DestinationConnectionString))
                {
                    string createMessageTable = sqlBuilder.CreateMessageTable();
                    db.SetCommand(createMessageTable).ExecuteNonQuery();

                    string createLoadMessageSP = sqlBuilder.CreateLoadMessageSP();
                    db.SetCommand(createLoadMessageSP).ExecuteNonQuery();
                }
            }
            catch
            {
            }
        }*/
    }
}
